/* Database Handler includes all the necessary functionality for the Android application
 * to interact with the local SQLite database, especially CRUD operations for objects of
 * Building or Inventory type.
 * 
 * Link for SQLite tutorial:
 * www.androidhive.info/2011/11/android-sqlite-database-tutorial/
 */
package com.example.wmuqrreader;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHandler extends SQLiteOpenHelper{

	// Global variables to be used throughout the DatabaseHandler class
	private static final int DATABASE_VERSION 	= 1;
	private static final String DATABASE_NAME 	= "fireInventory";
	private static final String TABLE_INVENTORY = "extinguisher";
	private static final String TABLE_BUILDING 	= "location";
	
	private static final String KEY_ID 			= "id";
	private static final String KEY_LOCATION	= "location";
	private static final String KEY_SERIAL		= "serial_number";
	private static final String KEY_TYPE		= "type";
	private static final String KEY_BRAND		= "brand";
	private static final String KEY_SERVICE		= "service_date";
	private static final String KEY_YEAR		= "manufactured_year";
	private static final String KEY_INVENTORY	= "inventory_number";
	
	private static final String KEY_NUM			= "building_number";
	private static final String KEY_BUILDING	= "building_name";
	
	// Constructor
	public DatabaseHandler(Context context){
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	//Only runs when the application is first installed.
	@Override
	public void onCreate(SQLiteDatabase db) {
		
		//Build a queries to create needed tables
		String CREATE_BUILDING_TABLE = "CREATE TABLE " + TABLE_BUILDING + "("
				+ KEY_ID		+ " INTEGER PRIMARY KEY,"
				+ KEY_NUM		+ " INTEGER,"
				+ KEY_BUILDING	+ " TEXT" + ")";
		
		String CREATE_INVENTORY_TABLE = "CREATE TABLE " + TABLE_INVENTORY + "("
				+ KEY_ID 		+ " INTEGER PRIMARY KEY,"
				+ KEY_LOCATION	+ " TEXT,"
				+ KEY_SERIAL	+ " INTEGER,"
				+ KEY_TYPE		+ " TEXT,"
				+ KEY_BRAND		+ " TEXT,"
				+ KEY_SERVICE	+ " TEXT,"
				+ KEY_YEAR		+ " TEXT,"
				+ KEY_INVENTORY	+ " INTEGER,"
				+ KEY_NUM		+ " INETEGR, FOREIGN KEY(" 
				+ KEY_NUM 		+ ") REFERENCES " 
				+ TABLE_BUILDING+ "(" + KEY_NUM + ") )";
		
		//Execute SQLite queries
		db.execSQL(CREATE_BUILDING_TABLE);
		db.execSQL(CREATE_INVENTORY_TABLE);	
		
	}

	//Only runs if changes are made to the database schema
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_BUILDING);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_INVENTORY);
 
        // Create tables again
        onCreate(db);
	}
	
	/*--------------------------------------------------------------------------*/
	/*				CRUD Operations - Create, Read, Update, Delete 				*/
	/*--------------------------------------------------------------------------*/
	
	//Inventory CRUD Operations
	
	//CREATE: Adds a new fire extinguisher to the database
	public void addInventory(Inventory inventory){
		Log.d("SAVE: ", "ID: " + inventory.get_id() + " LOC: " + inventory.get_location() + "BLDG: " + inventory.get_building());
		SQLiteDatabase db = this.getWritableDatabase();
		 
	    ContentValues values = new ContentValues();
	    values.put(KEY_LOCATION, inventory.get_location());	// Inventory location
	    values.put(KEY_SERIAL, inventory.get_serialNum());	// Inventory Serial
	    values.put(KEY_TYPE, inventory.get_type());
	    values.put(KEY_BRAND, inventory.get_brand());
	    values.put(KEY_SERVICE, inventory.get_svDate());
	    values.put(KEY_YEAR, inventory.get_year());
	    values.put(KEY_INVENTORY, inventory.get_inv());
	    values.put(KEY_NUM, inventory.get_building());
	 
	    // Inserting Row
	    long result = db.insert(TABLE_INVENTORY, null, values);
	    Log.d("SAVE: ", "" + result);
	    db.close();
	}
	
	//READ: Getting a single inventory entry
	public Inventory getInventory(int id){
		    SQLiteDatabase db = this.getReadableDatabase();
		 
		    Cursor cursor = db.query(TABLE_INVENTORY, new String[] { KEY_ID,
		            KEY_LOCATION, KEY_SERIAL, KEY_TYPE, KEY_BRAND, KEY_SERVICE, KEY_YEAR, KEY_INVENTORY, KEY_NUM }, KEY_ID + "=?",
		            new String[] { String.valueOf(id) }, null, null, null, null);
		    
		    //TODO: A More robust way of determining if record DNE might be prudent...
		    try {
		    	if (cursor != null)
		    		cursor.moveToFirst();
		 
			    Inventory inventory = new Inventory(cursor.getInt(0),
			            cursor.getString(1), cursor.getString(2), cursor.getString(3), 
			            cursor.getString(4), cursor.getString(5), cursor.getString(6), cursor.getInt(7), cursor.getInt(8));
			    
			    // return entry
			    return inventory;
		    } 
		    catch(Exception e) {
		    	return new Inventory(-1);
		    }
	}
	
	//TODO: Get inventory by inv#
	
	//READ: Getting all entries
	public List<Inventory> getAllInventory(int bldg_id){
		
		Log.d("READ: ", "ENTRIES FROM ");
		
		List<Inventory> inventoryList = new ArrayList<Inventory>();
	 
	    SQLiteDatabase db = this.getWritableDatabase();
	    
	    // Select All Query - order by id ASC
        Cursor cursor = db.query(true, TABLE_INVENTORY, null, KEY_NUM + "=" + bldg_id, null, null, null, KEY_ID + " ASC", null);

	 
	    // looping through all rows and adding to list
	    if (cursor.moveToFirst()) {
	        do {
	            Inventory inv = new Inventory();
	            inv.set_id(cursor.getInt(0));
	            inv.set_location(cursor.getString(1));
	            inv.set_serialNum(cursor.getString(2));
	            inv.set_type(cursor.getString(3));
	            inv.set_brand(cursor.getString(4));
	            inv.set_svDate(cursor.getString(5));
	            inv.set_year(cursor.getString(6));
	            inv.set_inv(cursor.getInt(7));
	            inv.set_building(cursor.getInt(8));
	            
	            Log.d("READ: ", "ID: "+ inv.get_id() +", LOC: " + inv.get_location() );
	            
	            // Adding contact to list
	            inventoryList.add(inv);
	        } while (cursor.moveToNext());
	    }	  
	    
	    Log.d("READ:", "COMPLETE");
	    
	    // return inventory list
	    return inventoryList;
	}
	
	//READ: TODO: get inventory count
	public int getInventoryCount(){
		return -1;
	}
	
	//UPDATE: Update inventory entry
	public int updateInventory(Inventory inventory){
		SQLiteDatabase db = this.getWritableDatabase();
		 
	    ContentValues values = new ContentValues();
	    values.put(KEY_LOCATION, inventory.get_location());	// Inventory location
	    values.put(KEY_SERIAL, inventory.get_serialNum());	// Inventory Serial
	    values.put(KEY_TYPE, inventory.get_type());
	    values.put(KEY_BRAND, inventory.get_brand());
	    values.put(KEY_SERVICE, inventory.get_svDate());
	    values.put(KEY_YEAR, inventory.get_year());
	    values.put(KEY_INVENTORY, inventory.get_inv());
	    
	    // updating row
	    return db.update(TABLE_INVENTORY, values, KEY_ID + " = ?",
	            new String[] { String.valueOf(inventory.get_id()) });
	}
	
	//DELETE: remove an entry from the db
	public void deleteInventory(Inventory inventory){
		Log.d("DEL: ", "ID: " + inventory.get_id() + " LOC: " + inventory.get_location());
		SQLiteDatabase db = this.getWritableDatabase();
	    int result = db.delete(TABLE_INVENTORY, KEY_ID + " = ?", new String[] { String.valueOf(inventory.get_id()) });
	    
	    if(result == 1)
	    	Log.d("DEL:", "SUCCESS");
	    else
	    	Log.d("DEL:", "FAILED");
	    
	    db.close();
	}

	//TODO: BUILDING CRUD operations (confirmed)
	
	//CREATE: Add NEW building
	public void addBuilding(Building building){
		Log.d("SAVE: ", "ID: " + building.get_id() + " LOC: " + building.get_building());	// Write a message to the log
		SQLiteDatabase db = this.getWritableDatabase();		//Connect to the android device db
		 
	    ContentValues values = new ContentValues();
	    values.put(KEY_NUM, building.get_num());	// Inventory location
	    values.put(KEY_BUILDING, building.get_building());	// Inventory Serial
	 
	    // Inserting Row
	    long result = db.insert(TABLE_BUILDING, null, values);
	    Log.d("SAVE: ", "" + result);
	    db.close();
	}
	
	//READ: Get all buildings from the db
	public List<Building> getAllBuilding() {
		Log.d("READ: ", "ENTRIES FROM ");
		
		//TODO: return all properties via building object
		List<Building> buildingList = new ArrayList<Building>();
	 
		// Connect to android device db
	    SQLiteDatabase db = this.getWritableDatabase();
	    
	    // Select All Query - order by id ASC
        Cursor cursor = db.query(true, TABLE_BUILDING, null, null, null, null, null, KEY_ID + " ASC", null);

	 
	    // looping through all rows and adding to list
	    if (cursor.moveToFirst()) {
	        do {
	            Building bldg = new Building();
	            bldg.set_id(cursor.getInt(0));
	            bldg.set_num(cursor.getInt(1));
	            bldg.set_building(cursor.getString(2));
	            
	            Log.d("READ: ", "ID: "+ bldg.get_id() +", LOC: " + bldg.get_building() );
	            
	            // Adding contact to list
	            buildingList.add(bldg);
	        } while (cursor.moveToNext());
	    }	  
	    
	    Log.d("READ:", "COMPLETE");
	    
	    // return building list
	    return buildingList;
	}	
}
